<%@page import="com.mde.util.Validator"%>
<%@page import="com.mde.util.Point"%>
<%@page import="com.mde.util.Counter"%>
<%@page import="java.util.ArrayList"%>
<%@page import="java.util.List"%>
<%@ page import="java.sql.*" %>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Adding route to DB</title>
</head>
<body>
<%!
	Statement stmt;
	Connection con;
	String url = Validator.dbURL;	
%>
<%

	if (request.getParameter("routeName").equals("")) {
		out.println("Empty route Name");
		return;
	}

	List<Double> lats = new ArrayList<Double>();
	List<Double> lons = new ArrayList<Double>();
	List<Integer> ids = new ArrayList<Integer>();
	List<Integer> order = new ArrayList<Integer>();

	Class.forName("com.mysql.jdbc.Driver");
	con = DriverManager.getConnection(url, "root", "12345"); 
	stmt = con.createStatement();
	
	
	
	String checkQueryRus = "SELECT * FROM Routes WHERE name = \"" + request.getParameter("routeName") + "\";";
	String checkQueryUTF = new String(checkQueryRus.getBytes("ISO8859_1"), "UTF8");   
	ResultSet check = stmt.executeQuery(checkQueryUTF);
	if (check.next()) {
		out.println("Route name exists");
		return;
	}
	
	int pointsNumber = 0;
	String paramName = Integer.toString(pointsNumber++);
	String param = request.getParameter(paramName);
	
	StringBuilder set = new StringBuilder("(");
	
	while (param != null) {
		set.append(param);
		order.add(Integer.parseInt(param));
		paramName = Integer.toString(pointsNumber++);
		param = request.getParameter(paramName);
		if (param != null) set.append(", ");
	}
	
	set.append(")");

	String query = "SELECT id, latitude, longitude FROM Points WHERE id IN " +
			set +
			";";
				
	ResultSet rs = stmt.executeQuery(query);
	
	while(rs.next()){
        //Retrieve by column name
        int id  = rs.getInt("id");
        double latitude = rs.getDouble("latitude");
        double longitude = rs.getDouble("longitude");
        		
        ids.add(id);
        lats.add(latitude);
        lons.add(longitude);  
     }

	rs = stmt.executeQuery("SELECT MAX(routeId) AS routeId FROM Edges");
	int newRouteId = 0;
	while (rs.next())
		newRouteId = rs.getInt("routeId") + 1;
 	out.println("New route id = " + newRouteId + ". Route successfully added!");
	int len = order.size();
	Point point1 = Point.findPoint(ids, lats, lons, order.get(0));
	for (int i = 1; i < len; ++i) {
		Point point2 = Point.findPoint(ids, lats, lons, order.get(i));
		double weight = Counter.WEIGHT_MULTIPLIER *  
				Counter.getDistance(point1.getLat(), point1.getLon(), 
				point2.getLat(), point2.getLon());
		
		String insert_query = 
				"INSERT INTO Edges (fromId, whereId, routeId, weight) " + 
				"VALUES (" + point1.getId() + 
				", " + point2.getId() +  
				", " + newRouteId +
				", " + weight + 
				");";
		query = new String(insert_query.getBytes("ISO8859_1"), "UTF8"); 
		
		stmt.execute(query);
			
		point1 = point2;
	}
	
	List<String> insert_queries = new ArrayList<String>();
	rs = stmt.executeQuery("SELECT id FROM Edges WHERE routeId = " + newRouteId + ";");
	int edgeOrder = 0;
	while (rs.next()) {
		String insert_query = 
				"INSERT INTO Routes (edgeId, edgeOrder, type, routeId, name) " + 
				"VALUES (" + rs.getInt("id") +
				", " + edgeOrder++ +   
				", " + "\"taxi\"" +
				", " + newRouteId + 
				", \"" + request.getParameter("routeName") +
				"\");";
		insert_queries.add(insert_query);		
	}
	
	len = insert_queries.size();
	
	for (int i = 0; i < len; ++i)
		stmt.execute(insert_queries.get(i));

	rs.close();
	stmt.close();
	con.close();	
%>	

</body>
</html>